﻿CREATE FUNCTION [dbo].[fs_Verify_Base]
	(
	@StateID int
	)
RETURNS TABLE
AS

RETURN 
SELECT     TOP (100) PERCENT
v_List_Base.LoanID,
v_List_Base.Status,
CONVERT(varchar,v_List_Base.StatusDate,111) AS [Date In],
v_List_Base.FirstName AS [First Name],
v_List_Base.LastName AS [Last Name], 
ContractHolder,
dbo.sprintf1('<DIV style="color:$1;TEXT-ALIGN:right">$0</DIV>', STR(ISNULL(VDP.[Done%], 0), 3), 
              CASE WHEN VDP.[Done%] >= 75 THEN 'DARKGREEN' ELSE 'CRIMSON' END) AS [Done%],
v_List_Base.VIN,
v_List_Base.Description,
v_List_Base.Dealer
FROM v_List_Base
INNER JOIN dbo.fs_Verify_Read(DEFAULT, @StateID) AS fs_Loans ON v_List_Base.LoanID = fs_Loans.LoanID
LEFT OUTER JOIN
 (SELECT LoanID, MenuID, [Done%]  FROM v_VerificationDonePerc  WHERE (MenuID = @StateID)) AS VDP
	ON fs_Loans.LoanID = VDP.LoanID
/*
INNER JOIN
                          (SELECT     LoanID, MenuID, COUNT(*) AS Count
                            FROM          t_Loan_Menus
                            GROUP BY LoanID, MenuID
                            HAVING      (MenuID = @MenuID)) AS MENU_COUNT ON v_List_Base.LoanID = MENU_COUNT.LoanID LEFT OUTER JOIN
                          (SELECT     LoanID, MAX(DateStamp) AS DateStamp
                            FROM          t_Notes AS t_Notes_1
                            GROUP BY LoanID) AS t_Notes ON v_List_Base.LoanID = t_Notes.LoanID
*/









